﻿using System.Data;
using System.Data.Common;

using ZhangBY.Data;
using ZhangBY.Entity;
using ZhangBY.Data.IDbSupplier;


namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataSupplier
    {
        /// <summary>
        /// 根據用戶名和模塊代號取得用戶模塊權限
        /// </summary>
        /// <param name="Account">用戶名</param>
        /// <param name="ModuleCode">模塊代號</param>
        /// <returns></returns>
        public DataSet GetCompanyModulePermissions(string Account, int indexName)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@account",(DbType)SqlDbType.NVarChar,Account),
                                     DBHelper.CreateInParam("@indexName",(DbType)SqlDbType.NVarChar,indexName),
                                 };
            string commandText = "SELECT DISTINCT MAX([dbo].[zby_CA_CompanyPermissions].[All])AS [All], MAX([dbo].[zby_CA_CompanyPermissions].[Read])AS [READ], MAX([dbo].[zby_CA_CompanyPermissions].[Edit])AS [EDIT],";
            commandText += " MAX([dbo].[zby_CA_CompanyPermissions].[Delete])AS [DELETE], MAX([dbo].[zby_CA_CompanyPermissions].[Add])AS [ADD]";
            commandText += "FROM [dbo].[zby_CA_CompanyUserRoles] INNER JOIN [dbo].[zby_CA_CompanyUser] ON ";
            commandText+="[dbo].[zby_CA_CompanyUserRoles].[UserId] = [dbo].[zby_CA_CompanyUser].[ID] INNER JOIN [dbo].[zby_CA_CompanyRoles] ON ";
            commandText+="[dbo].[zby_CA_CompanyUserRoles].[RolesId] = [dbo].[zby_CA_CompanyRoles].[ID] INNER JOIN [dbo].[zby_CA_CompanyModule] INNER JOIN";
            commandText+="[dbo].[zby_CA_CompanyRolesModule] ON [dbo].[zby_CA_CompanyModule].[ID] = dbo.zby_CA_CompanyRolesModule.ModuleID ON";
            commandText+="[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesModule].[RolesID] INNER JOIN";
            commandText+="[dbo].[zby_CA_CompanyPermissions] INNER JOIN [dbo].[zby_CA_CompanyRolesPermissions] ON ";
            commandText+="[dbo].[zby_CA_CompanyPermissions].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[PermissionsId] ON ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[RolesId]";
            commandText += "WHERE  ([dbo].[zby_CA_CompanyUser].[Account] =@account AND  [dbo].[zby_CA_CompanyModule].[IndexName]=@indexName);";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名和模塊名稱取得用戶模塊權限
        /// </summary>
        /// <param name="Account">用戶名</param>
        /// <param name="ModuleName">模塊名</param>
        /// <returns></returns>
        public DataSet GetCompanyModulePermissions(string Account, string ModuleName)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@account",(DbType)SqlDbType.NVarChar,50,Account),
                                     DBHelper.CreateInParam("@modulename",(DbType)SqlDbType.NVarChar,50,ModuleName),
                                 };

            string commandText = "SELECT DISTINCT  MAX([dbo].[zby_CA_CompanyPermissions].[All])AS [All], MAX([dbo].[zby_CA_CompanyPermissions].[Read])AS [READ], MAX([dbo].[zby_CA_CompanyPermissions].[Edit])AS [EDIT],";
            commandText += " MAX([dbo].[zby_CA_CompanyPermissions].[Delete])AS [DELETE], MAX([dbo].[zby_CA_CompanyPermissions].[Add])AS [ADD]";
            commandText += "FROM [dbo].[zby_CA_CompanyModule] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyMenu] ON [dbo].[zby_CA_CompanyModule].[ID] = [dbo].[zby_CA_CompanyMenu].[ModuleID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyPermissions] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRolesPermissions] ON [dbo].[zby_CA_CompanyPermissions].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[PermissionsId] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRoles] ON [dbo].[zby_CA_CompanyRolesPermissions].[RolesId] = [dbo].[zby_CA_CompanyRoles].[ID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUserRoles] ON [dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyUserRoles].[RolesId] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUser] ON [dbo].[zby_CA_CompanyUserRoles].[UserId] = [dbo].[zby_CA_CompanyUser].[ID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRolesModule] ON [dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesModule].[RolesID] ON ";
            commandText += "[dbo].[zby_CA_CompanyModule].[ID] = [dbo].[zby_CA_CompanyRolesModule].[ModuleID]";
            commandText += "WHERE  ([dbo].[zby_CA_CompanyUser].[Account] =@account AND  [dbo].[zby_CA_CompanyModule].[name]=@modulename);";

            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據帳號取得所有模塊角色權限
        /// </summary>
        /// <param name="Account">帳號</param>
        /// <returns></returns>
        public DataSet GetCompanyAllModulePermissions(string Account)
        {
            DbParameter getPar =DBHelper.CreateInParam("@account",(DbType)SqlDbType.NVarChar,50,Account);

            string commandText = "SELECT DISTINCT [dbo].[zby_CA_CompanyModule].[indexName],[dbo].[zby_CA_CompanyModule].[Name],";
            commandText += "MAX([dbo].[zby_CA_CompanyPermissions].[All])AS [All],MAX([dbo].[zby_CA_CompanyPermissions].[Read])AS [READ], MAX([dbo].[zby_CA_CompanyPermissions].[Edit])AS [EDIT],";
            commandText += "MAX([dbo].[zby_CA_CompanyPermissions].[Delete])AS [DELETE], MAX([dbo].[zby_CA_CompanyPermissions].[Add])AS [ADD]";
            commandText += "FROM [dbo].[zby_CA_CompanyUserRoles] INNER JOIN [dbo].[zby_CA_CompanyUser] ON ";
            commandText += "[dbo].[zby_CA_CompanyUserRoles].[UserId] = [dbo].[zby_CA_CompanyUser].[ID] INNER JOIN [dbo].[zby_CA_CompanyRoles] ON ";
            commandText += "[dbo].[zby_CA_CompanyUserRoles].[RolesId] = [dbo].[zby_CA_CompanyRoles].[ID] INNER JOIN [dbo].[zby_CA_CompanyModule] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRolesModule] ON [dbo].[zby_CA_CompanyModule].[ID] = dbo.zby_CA_CompanyRolesModule.ModuleID ON";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyPermissions] INNER JOIN [dbo].[zby_CA_CompanyRolesPermissions] ON ";
            commandText += "[dbo].[zby_CA_CompanyPermissions].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[PermissionsId] ON ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[RolesId]";
            commandText += "WHERE  ([dbo].[zby_CA_CompanyUser].[Account] =@account) GROUP BY [dbo].[zby_CA_CompanyModule].[IndexName],[dbo].[zby_CA_CompanyModule].[Name];";

            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名取得該用戶隸屬角色
        /// </summary>
        /// <param name="Account"></param>
        /// <returns></returns>
        public DataSet GetCompanyAllUserRoles(string Account)
        {
            DbParameter getPar = DBHelper.CreateInParam("@account", (DbType)SqlDbType.NVarChar, 50, Account);

            string commandText = null;
            commandText += "SELECT   [dbo].[zby_CA_CompanyUserRoles].[id],[dbo].[zby_CA_CompanyUser].[Account], [dbo].[zby_CA_CompanyRoles].[RolesName],[dbo].[zby_CA_CompanyUserRoles].[UserId],[dbo].[zby_CA_CompanyUserRoles].[RolesId]";
            commandText += "FROM     [dbo].[zby_CA_CompanyRoles] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUserRoles] ON [dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyUserRoles].[RolesId] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUser] ON [dbo].[zby_CA_CompanyUserRoles].[UserId] = [dbo].[zby_CA_CompanyUser].[ID]";
            commandText += "WHERE     ([dbo].[zby_CA_CompanyUser].[Account] = @account)";
            //commandText += "GROUP BY [dbo].[zby_CA_CompanyUser].[Account], [dbo].[zby_CA_CompanyRoles].[RolesName],[dbo].[zby_CA_CompanyUserRoles].[UserId],[dbo].[zby_CA_CompanyUserRoles].[RolesId]";

            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);

        }
        /// <summary>
        /// 根據用戶名取得該用戶擁有操作模塊資源
        /// </summary>
        /// <param name="Account"></param>
        /// <returns></returns>
        public DataSet GetCompanyAllUserModule(string Account)
        {
            DbParameter getPar = DBHelper.CreateInParam("@account", (DbType)SqlDbType.NVarChar, 50, Account);

            string commandText = null;
            commandText += "SELECT     [dbo].[zby_CA_CompanyUser].[Account], [dbo].[zby_CA_CompanyModule].[Name], [dbo].[zby_CA_CompanyModule].[IndexName]";
            commandText += "FROM       [dbo].[zby_CA_CompanyUserRoles] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyUser] ON [dbo].[zby_CA_CompanyUserRoles].[UserId] = [dbo].[zby_CA_CompanyUser].[ID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRoles] ON [dbo].[zby_CA_CompanyUserRoles].[RolesId] = [dbo].[zby_CA_CompanyRoles].[ID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyModule] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRolesModule] ON [dbo].[zby_CA_CompanyModule].[ID] = [dbo].[zby_CA_CompanyRolesModule].[ModuleID] ON ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesModule].[RolesID]";
            commandText += "WHERE     (dbo.zby_CA_CompanyUser.Account = 'admin')";
            commandText += "GROUP BY [dbo].[zby_CA_CompanyUser].[Account], [dbo].[zby_CA_CompanyModule].[Name], [dbo].[zby_CA_CompanyModule].[IndexName]";

            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得所有模塊資源
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyAllModule()
        {

            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyModule]";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText);
        }           
        /// <summary>
        /// 創建一個新角色
        /// </summary>
        /// <param name="RolesName">角色名</param>
        /// <returns></returns>
        public int AddNewCompanyRoles(string RolesName)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolesName", (DbType)SqlDbType.NVarChar, 50, RolesName);

            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyRoles]([RolesName]) VALUES(@rolesName)";

            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 創建一個新角色并返回角色ID
        /// </summary>
        /// <param name="RolesName">角色名</param>
        /// <returns></returns>
        public object AddNewCompanyRolesReturnId(string RolesName)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolesName", (DbType)SqlDbType.NVarChar, 50, RolesName);

            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyRoles]([RolesName]) VALUES(@rolesName);SELECT @@IDENTITY";

            return DBHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色名刪除角色
        /// </summary>
        /// <param name="RolesName"></param>
        /// <returns></returns>
        public int DeleteCompanyRoles(string RolesName)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolesName", (DbType)SqlDbType.NVarChar, 50, RolesName);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyRoles] WHERE [dbo].[zby_CA_CompanyRoles].[name]=@rolesname";

            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色主鍵ID刪除角色
        /// </summary>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int DeleteCompanyRoles(int RolesId)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolesid", (DbType)SqlDbType.Int, int.MaxValue, RolesId);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyRoles] WHERE [dbo].[zby_CA_CompanyRoles].[name]=@rolesid";

            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ROLES表組件ID更改角色名
        /// </summary>
        /// <param name="RolesId"></param>
        /// <param name="RolesName"></param>
        /// <returns></returns>
        public int UpdateCompanyRolesName(int RolesId, string RolesName)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                     DBHelper.CreateInParam("@name",(DbType)SqlDbType.NVarChar,50,RolesName),
                                 };
            string commandText = "UPDATE [dbo].[zby_CA_CompanyRoles] SET [name]=@name WHERE [ID]=@rolesid";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加（分配）用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int AddNewCompanyUserRoles(int UserId, int RolesId)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId),
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                 };
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyUserRoles]([UserId],[RolesId])VALUES(@userid,@rolesid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加（分配）用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int AddNewCompanyUserRoles(string UserId, string RolesId)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId),
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                 };
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyUserRoles]([UserId],[RolesId])VALUES(@userid,@rolesid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據UserRoles主鍵ID刪除用戶角色
        /// </summary>
        /// <param name="UserRolesId"></param>
        /// <returns></returns>
        public int DeleteCompanyUserRoles(int UserRolesId)
        {
            DbParameter getPar =DBHelper.CreateInParam("@userrolesid",(DbType)SqlDbType.Int,int.MaxValue,UserRolesId);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyUserRoles] WHERE ([id]=@userrolesid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 刪除用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int DeleteCompanyUserRoles(int UserId, int RolesId)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId),
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                 };
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyUserRoles] WHERE ([userid]=@userid AND [rolesid]=@rolesid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據指定用戶ID刪除用戶角色
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="RolesId"></param>
        /// <returns></returns>
        public int DeleteCompanyUserRoles(string UserId)
        {
            DbParameter getPar =DBHelper.CreateInParam("@userid",(DbType)SqlDbType.Int,int.MaxValue,UserId);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyUserRoles] WHERE ([userid]=@userid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名取得用戶主鍵ID
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public object GetCompanyUserID(string UserName)
        {
            DbParameter getPar =DBHelper.CreateInParam("@account",(DbType)SqlDbType.NVarChar,50,UserName);
            string commandText = "SELECT [ID] FROM [dbo].[zby_CA_CompanyUser] WHERE([account]=@account)";
            return DBHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色名取得角色主鍵ID
        /// </summary>
        /// <param name="RolesName"></param>
        /// <returns></returns>
        public object GetCompanyRolesID(string RolesName)
        {
            DbParameter getPar = DBHelper.CreateInParam("@name", (DbType)SqlDbType.NVarChar, 50, RolesName);
            string commandText = "SELECT [ID] FROM [dbo].[zby_CA_CompanyRoles] WHERE([rolesname]=@name)";
            return DBHelper.ExecuteScalar(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得角色表所有信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyRolesInfo()
        {
            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyRoles]";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得角色模塊資源
        /// </summary>
        /// <param name="rolesid"></param>
        /// <returns></returns>
        public DataSet GetCompanyRolesModule(string rolesid)
        {
            DbParameter getPar = DBHelper.CreateInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,@rolesid);
            string commandText = "";
            commandText += "SELECT [dbo].[zby_CA_CompanyRoles].[RolesName], [dbo].[zby_CA_CompanyModule].[Name] AS [ModuleName],";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] AS [RolesId], [dbo].[zby_CA_CompanyModule].[IndexName],";
            commandText += "[dbo].[zby_CA_CompanyRolesModule].[ModuleID],[dbo].[zby_CA_CompanyRolesModule].[ID] AS [RolesModuleId]";
            commandText += "FROM [dbo].[zby_CA_CompanyRoles] INNER JOIN [dbo].[zby_CA_CompanyRolesModule] ON ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyModule] ON [dbo].[zby_CA_CompanyRolesModule].[ModuleID] = [dbo].[zby_CA_CompanyModule].[ID]";
            commandText += "WHERE [dbo].[zby_CA_CompanyRoles].[ID]=@id";

            return DBHelper.ExecuteDataSet(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得權限類表全部記錄信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyPermissionsInfo()
        {
            string commandText = "SELECT * FROM [dbo].[zby_CA_CompanyPermissions]";
            return DBHelper.ExecuteDataSet(CommandType.Text,commandText);
        }
        /// <summary>
        /// 添加一組操作權限
        /// </summary>
        /// <param name="All"></param>
        /// <param name="Read"></param>
        /// <param name="Edit"></param>
        /// <param name="Delete"></param>
        /// <param name="Add"></param>
        /// <returns></returns>
        public int AddNewCompanyPermissions(PermissionsInfo Per)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@all",(DbType)SqlDbType.Int,Per.All),
                                     DBHelper.CreateInParam("@read",(DbType)SqlDbType.Int,Per.Read),
                                     DBHelper.CreateInParam("@edit",(DbType)SqlDbType.Int,Per.Edit),
                                     DBHelper.CreateInParam("@delete",(DbType)SqlDbType.Int,Per.Delete),
                                     DBHelper.CreateInParam("@add",(DbType)SqlDbType.Int,Per.Add)
                                 };
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyPermissions]([All],[Read],[Edit],[Delete],[Add])VALUES(@all,@read,@edit,@delete,@add)";

            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據權限主鍵ID刪除權限組
        /// </summary>
        /// <param name="PermissionsId"></param>
        /// <returns></returns>
        public int DeleteCompanyPermissions(int PermissionsId)
        {
            DbParameter getPar = DBHelper.CreateInParam("@permissionsid", (DbType)SqlDbType.Int, int.MaxValue, PermissionsId);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyPermissions] WHERE ([id]=@permissionsid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加（分配）角色權限
        /// </summary>
        /// <param name="RolesId"></param>
        /// <param name="PermissionsId"></param>
        /// <returns></returns>
        public int AddNewCompanyRolesPermissions(int RolesId, int PermissionsId)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                     DBHelper.CreateInParam("@permissionsid",(DbType)SqlDbType.Int,int.MaxValue,PermissionsId),
                                 };
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyRolesPermissions]([RolesId],[PermissionsId])VALUES(@rolesid,@permissionsid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 刪除角色權限
        /// </summary>
        /// <param name="RolesId"></param>
        /// <param name="PermissionsId"></param>
        /// <returns></returns>
        public int DeleteCompanyRolesPermissions(int RolesId, int PermissionsId)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RolesId),
                                     DBHelper.CreateInParam("@permissionsid",(DbType)SqlDbType.Int,int.MaxValue,PermissionsId),
                                 };
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyRolesPermissions] WHERE ([rolesid]=@rolesid AND [permissionsid]=@permissionsid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據RolesPermissions主鍵ID刪除角色權限
        /// </summary>
        /// <param name="RolesPermissionsId"></param>
        /// <returns></returns>
        public int DeleteCompanyRolesPermissions(int RolesPermissionsId)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolespermissionsid", (DbType)SqlDbType.Int, int.MaxValue, RolesPermissionsId);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyPermissions] WHERE ([id]=@rolespermissionsid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加新操作模塊
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int AddNewCompanyModule(ModuleInfo MInfo)
        {
            DbParameter getPar = DBHelper.CreateInParam("@modulename", (DbType)SqlDbType.NVarChar, 50, MInfo.Name);
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyModule]([Name])VALUES(@modulename)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID更新模塊名
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int UpdateCompanyModuleName(ModuleInfo MInfo)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@moduleid",(DbType)SqlDbType.Int,int.MaxValue,MInfo.ID),
                                     DBHelper.CreateInParam("@modulename",(DbType)SqlDbType.Int,int.MaxValue,MInfo.Name),
                                 };
            string commandText = "UPDATE [dbo].[zby_CA_CompanyModule] SET [name]=@modulename WHERE [ID]=@moduleid";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID刪除模塊
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int DeleteCompanyModuleById(ModuleInfo MInfo)
        {
            DbParameter getPar = DBHelper.CreateInParam("@moduleid", (DbType)SqlDbType.NVarChar, 50, MInfo.ID);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyModule] WHERE ([id]=@moduleid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊名刪除模塊
        /// </summary>
        /// <param name="MInfo"></param>
        /// <returns></returns>
        public int DeleteCompanyModuleByName(ModuleInfo MInfo)
        {
            DbParameter getPar = DBHelper.CreateInParam("@modulename", (DbType)SqlDbType.NVarChar, 50, MInfo.Name);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyModule] WHERE ([name]=@modulename)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 給角色指定（添加）操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int AddNewCompanyRolesModule(RolesModuleInfo RMinfo)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@moduleid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.ModuleID),
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.RolesID),
                                 };
            string commandText = "INSERT INTO [dbo].[zby_CA_CompanyRolesModule]([RolesId],[ModuleId])VALUES(@rolesid,@moduleid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據主鍵ID刪除角色操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int DeleteCompanyRolesModuleById(RolesModuleInfo RMinfo)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolesmoduleid", (DbType)SqlDbType.NVarChar, 50, RMinfo.ID);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyRolesModule] WHERE ([id]=@rolesmoduleid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據角色ID刪除角色操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int DeleteCompanyRolesModuleByRolesId(RolesModuleInfo RMinfo)
        {
            DbParameter getPar = DBHelper.CreateInParam("@rolesid", (DbType)SqlDbType.Int,int.MaxValue, RMinfo.RolesID);
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyRolesModule] WHERE ([rolesid]=@rolesid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID和角色ID刪除角色操作模塊
        /// </summary>
        /// <param name="RMinfo"></param>
        /// <returns></returns>
        public int DeleteCompanyRolesModuleByRolesIdAndModuleId(RolesModuleInfo RMinfo)
        {
            DbParameter[] getPar ={
                                     DBHelper.CreateInParam("@moduleid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.ModuleID),
                                     DBHelper.CreateInParam("@rolesid",(DbType)SqlDbType.Int,int.MaxValue,RMinfo.RolesID),
                                 };
            string commandText = "DELETE FROM [dbo].[zby_CA_CompanyRolesModule] WHERE ([rolesid]=@rolesid AND [moduleid]=@moduleid)";
            return DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 取得所有模塊與角色與權限信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyAllRolesModulePermissions()
        {
            string commandText = "SELECT ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[RolesName], [dbo].[zby_CA_CompanyRoles].[ID] AS [RolesId], ";
            commandText += "[dbo].[zby_CA_CompanyModule].[Name] AS [ModuleName], [dbo].[zby_CA_CompanyModule].[IndexName], ";
            commandText += "MAX([dbo].[zby_CA_CompanyPermissions].[Read]) AS [Read],MAX([dbo].[zby_CA_CompanyPermissions].[Edit]) AS [Edit], ";
            commandText += "MAX([dbo].[zby_CA_CompanyPermissions].[Delete]) AS [Delete],MAX([dbo].[zby_CA_CompanyPermissions].[Add]) AS [Add]";
            commandText += "FROM ";
            commandText += "[dbo].[zby_CA_CompanyModule] INNER JOIN [dbo].[zby_CA_CompanyPermissions] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRoles] INNER JOIN [dbo].[zby_CA_CompanyRolesModule] ON ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[zby_CA_CompanyRolesPermissions] ON [dbo].[zby_CA_CompanyRoles].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[RolesId] ON";
            commandText += "[dbo].[zby_CA_CompanyPermissions].[ID] = [dbo].[zby_CA_CompanyRolesPermissions].[PermissionsId] ON ";
            commandText += "[dbo].[zby_CA_CompanyModule].[ID] = [dbo].[zby_CA_CompanyRolesModule].[ModuleID]";
            commandText += "GROUP BY ";
            commandText += "[dbo].[zby_CA_CompanyRoles].[RolesName], [dbo].[zby_CA_CompanyRoles].[ID],";
            commandText += "[dbo].[zby_CA_CompanyModule].[Name],[dbo].[zby_CA_CompanyModule].[IndexName]";
            return DBHelper.ExecuteDataSet(CommandType.Text,commandText);
        }
        /// <summary>
        /// 取得所有角色信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyAllRolesInfo()
        {
            string commandText = "Select *  FROM [dbo].[zby_CA_CompanyRoles] ";
            return DBHelper.ExecuteDataSet(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根據ID刪除角色和所有關聯表數據
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCompanyRolesAndInnerTable(string id)
        {
            DbParameter getPar = DBHelper.CreateInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);


            string commandText = "DELETE  FROM [dbo].[zby_CA_CompanyRolesModule] WHERE [rolesid]=@id";
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE  FROM [dbo].[zby_CA_CompanyRolesPermissions] WHERE [rolesid]=@id";
            i = DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE  FROM [dbo].[zby_CA_CompanyUserRoles] WHERE [rolesid]=@id";
            i = DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE  FROM [dbo].[zby_CA_CompanyRoles] WHERE [id]=@id";
            i = DBHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            return i;
        }
    }
}
